from datetime import datetime, time
from http.client import HTTPException

import pandas as pd
from sqlalchemy import delete, select, update
from sqlalchemy.ext.asyncio import AsyncSession
from module_statistics.entity.do.plant_service_do import PlantPlanterServices
from module_statistics.entity.vo.plant_service_vo import PlantServiceModel, PlantServicePageQueryModel
from utils.df_util import DfToObject, setChartType
from utils.page_util import PageUtil, PageResponseModel
from utils.mysql_util import MySQLHelper

mysql_cli = MySQLHelper()

class PlantServiceDao:
    """
    种植主体服务统计模块数据库操作层
    """

    @classmethod
    async def get_plant_service_detail_by_id(cls, db: AsyncSession, id: int):
        """
        根据记录id获取种植主体服务统计详细信息

        :param db: orm对象
        :param id: 记录id
        :return: 种植主体服务统计信息对象
        """
        plant_service_info = (
            (
                await db.execute(
                    select(PlantPlanterServices)
                    .where(
                        PlantPlanterServices.id == id
                    )
                )
            )
            .scalars()
            .first()
        )

        return plant_service_info

    @classmethod
    async def get_plant_service_detail_by_info(cls, db: AsyncSession, plant_service: PlantServiceModel):
        """
        根据种植主体服务统计参数获取种植主体服务统计信息

        :param db: orm对象
        :param plant_service: 种植主体服务统计参数对象
        :return: 种植主体服务统计信息对象
        """
        plant_service_info = (
            (
                await db.execute(
                    select(PlantPlanterServices).where(
                    )
                )
            )
            .scalars()
            .first()
        )

        return plant_service_info

    @classmethod
    async def get_plant_service_list(cls, db: AsyncSession, query_object: PlantServicePageQueryModel, is_page: bool = False):
        """
        根据查询参数获取种植主体服务统计列表信息

        :param db: orm对象
        :param query_object: 查询参数对象
        :param is_page: 是否开启分页
        :return: 种植主体服务统计列表信息对象
        """
        query = (
            select(PlantPlanterServices)
            .where(
                PlantPlanterServices.year.between(
                    datetime.combine(datetime.strptime(query_object.begin_year, '%Y-%m-%d'), time(00, 00, 00)),
                    datetime.combine(datetime.strptime(query_object.end_year, '%Y-%m-%d'), time(23, 59, 59)),
                )
                if query_object.begin_year and query_object.end_year
                else True,
                PlantPlanterServices.planter_id == query_object.planter_id if query_object.planter_id else True,
                PlantPlanterServices.service_month.between(
                    datetime.combine(datetime.strptime(query_object.begin_service_month, '%Y-%m-%d'), time(00, 00, 00)),
                    datetime.combine(datetime.strptime(query_object.end_service_month, '%Y-%m-%d'), time(23, 59, 59)),
                )
                if query_object.begin_service_month and query_object.end_service_month
                else True,
                PlantPlanterServices.service_type == query_object.service_type if query_object.service_type else True,
            )
            .order_by(PlantPlanterServices.id)
            .distinct()
        )
        plant_service_list = await PageUtil.paginate(db, query, query_object.page_num, query_object.page_size, is_page)

        return plant_service_list

    @classmethod
    async def get_planter_service_summary_list(cls, db: AsyncSession, query_object: PlantServicePageQueryModel):
        try:
            sql_str = (f"select year, planter_name, service_type, SUM(service_count) service_count_summary from plant_planter_services where year = { int(query_object.year) } group by year, planter_name, service_type ")
            df = mysql_cli.table_to_df(sql_str)
            planters = sorted(df['planter_name'].unique())
            # 2. 使用透视表重组数据
            pivot_df = df.pivot_table(
                index='service_type',
                columns='planter_name',
                values='service_count_summary',
                aggfunc='sum',  # 聚合方式：求和
                fill_value=0  # 缺失值填充为0
            ).reset_index()
            # 3. 按固定顺序重排列
            pivot_df = pivot_df[['service_type'] + planters]
            # 4. 将每行数据转换为列表
            pivot_df['data'] = pivot_df[planters].apply(lambda row: list(row), axis=1)

            # 5. 生成最终结果
            new_df = pivot_df[['service_type', 'data']].rename(
                columns={'service_type': 'name'}
            )
            new_df['type'] = 'bar'
            new_df['stack'] = "planter"
            plant_service_list = []
            for _, row in new_df.iterrows():
                obj = DfToObject(**row.to_dict())  # 将行字典解包为类属性
                plant_service_list.append(obj)
            result = PageResponseModel(
                rows=plant_service_list,
                total=len(plant_service_list),
                others = planters
            )
        except Exception as e:
            raise HTTPException(e)

        return result

    @classmethod
    async def get_service_type_summary_list(cls, db: AsyncSession, query_object: PlantServicePageQueryModel):
        try:
            sql_str = (
                f"select year, service_type, SUM(service_count) service_count_summary from plant_planter_services where year = {int(query_object.year)} group by year, service_type ")
            df = mysql_cli.table_to_df(sql_str)
            data_df = pd.DataFrame({
                'data': [
                    {'value': row['service_count_summary'], 'name': row['service_type']}
                    for _, row in df.iterrows()
                ]
            })
            nightingale_pie_dict = {
                'name': 'Nightingale Chart',
                'type': 'pie',
                'radius': [50, 150],
                'center': ['50%', '50%'],
                'roseType': 'area',
                'itemStyle': {'borderRadius': '8%'},
                'data': data_df['data'].tolist(),
            }
            plant_service_list = []# 将行字典解包为类属性
            plant_service_list.append(nightingale_pie_dict)
            result = PageResponseModel(
                rows=plant_service_list,
                total=len(plant_service_list),
            )
        except Exception as e:
            raise HTTPException(e)

        return result

    @classmethod
    async def get_service_monthly_summary_service(cls, db: AsyncSession, query_object: PlantServicePageQueryModel):
        try:
            sql_str = (f"select year, service_month, service_type, SUM(service_count) service_count_summary from plant_planter_services where year = { int(query_object.year) } group by year, service_month, service_type ")
            df = mysql_cli.table_to_df(sql_str)
            service_months = sorted(df['service_month'].unique())
            # 2. 使用透视表重组数据
            pivot_df = df.pivot_table(
                index='service_type',
                columns='service_month',
                values='service_count_summary',
                aggfunc='sum',  # 聚合方式：求和
                fill_value=0  # 缺失值填充为0
            ).reset_index()
            # 3. 按固定顺序重排列
            pivot_df = pivot_df[['service_type'] + service_months]
            # 4. 将每行数据转换为列表
            pivot_df['data'] = pivot_df[service_months].apply(lambda row: list(row), axis=1)

            # 5. 生成最终结果
            new_df = pivot_df[['service_type', 'data']].rename(
                columns={'service_type': 'name'}
            )
            # new_df['type'] = new_df['name'].apply(lambda x: setChartType(x))
            new_df['type'] = 'line'
            new_df['stack'] = "Total"
            plant_service_list = []
            for _, row in new_df.iterrows():
                obj = DfToObject(**row.to_dict())  # 将行字典解包为类属性
                plant_service_list.append(obj)
            result = PageResponseModel(
                rows=plant_service_list,
                total=len(plant_service_list),
                others = service_months
            )
        except Exception as e:
            raise HTTPException(e)
        return result


    @classmethod
    async def add_plant_service_dao(cls, db: AsyncSession, plant_service: PlantServiceModel):
        """
        新增种植主体服务统计数据库操作

        :param db: orm对象
        :param plant_service: 种植主体服务统计对象
        :return:
        """
        db_plant_service = PlantPlanterServices(**plant_service.model_dump(exclude={}))
        db.add(db_plant_service)
        await db.flush()

        return db_plant_service

    @classmethod
    async def edit_plant_service_dao(cls, db: AsyncSession, plant_service: dict):
        """
        编辑种植主体服务统计数据库操作

        :param db: orm对象
        :param plant_service: 需要更新的种植主体服务统计字典
        :return:
        """
        await db.execute(update(PlantPlanterServices), [plant_service])

    @classmethod
    async def delete_plant_service_dao(cls, db: AsyncSession, plant_service: PlantServiceModel):
        """
        删除种植主体服务统计数据库操作

        :param db: orm对象
        :param plant_service: 种植主体服务统计对象
        :return:
        """
        await db.execute(delete(PlantPlanterServices).where(PlantPlanterServices.id.in_([plant_service.id])))

